package com.metagurukul.metagranth.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import com.metagurukul.metagranth.model.common.connection.ConnectionPoolFactory;
import com.metagurukul.metagranth.model.common.dbutil.Util;
import com.metagurukul.metagranth.exception.MetaSqlException;
import com.metagurukul.metagranth.util.Validator;
import com.metagurukul.metagranth.vo.User;
import com.mysql.jdbc.Statement;

import java.sql.ResultSet;

/**
 * 
 * @author team metaGranth
 * this class will perform all the functions corresponding to the user table
 *
 */
public class UserDAO {

	/**
	 * this function will save the complete record of user in the database
	 * 
	 * @param user stores the user value object
	 * @return	status whether the user has been saved in the database or not
	 */
	public static int save(User user) throws MetaSqlException {
		//create and retrieve the connection from the ConnectionPoolFactory
		ConnectionPoolFactory cp = ConnectionPoolFactory.getInstance();
		Connection con = cp.checkOut();
		
		
		String uname = user.getUsername();
		String password = user.getPassword();
		String name = user.getName();
		int age = user.getAge();

		String query = "INSERT INTO metacubegranth_user (USER_NAME,PASSWORD,NAME,AGE) VALUES (?,?,?,?) ";

		PreparedStatement ps = null;
		int status = -1;
		try {
			
			ps = con.prepareStatement(query);
			/* set variable in prepared statement */
			ps.setString(1, uname);
			ps.setString(2, password);
			ps.setString(3, name);
			ps.setInt(4, age);
			status = ps.executeUpdate();
		} catch (SQLException e) {
			//if there is any sql exception  then the exception is thrown
			throw new MetaSqlException(e);
		} finally {
			//close all the open database connection
			Util.closeStatement(ps);
			//checkIn the connection again to the ConnectionPoolFactory
			 cp.checkIn(con);
		}
		return status;
	}

	/**
	 * this function will return the entire details of the username given as an
	 * argument to this function
	 * 
	 * @param user	stores the user value object
	 * @return	the user value object after performing search operation
	 * @throws metaSqlException
	 */
	public static User userSet(User user) throws MetaSqlException {
		//create and retrieve the connection from the ConnectionPoolFactory
		ConnectionPoolFactory cp = ConnectionPoolFactory.getInstance();
		Connection con = cp.checkOut();
		
		String uname = user.getUsername();
		
		ResultSet rs = null;
		User loggedUser = null;
		
		String query = " SELECT * FROM metacubegranth_user WHERE USER_NAME LIKE ? ";
		PreparedStatement ps = null;
		try {
			
			ps = con.prepareStatement(query);
			/* set variable in prepared statement */
			ps.setString(1, uname);
			rs = ps.executeQuery();
			loggedUser = processResultSet(rs);
		} catch (SQLException e) {
			//if there is any sql exception  then the exception is thrown
			throw new MetaSqlException(e);
		} finally {
			//close all the open database connection
			Util.closeStatement(ps);
			//checkIn the connection again to the ConnectionPoolFactory
			 cp.checkIn(con);
		}
		return loggedUser;
	}
	
	/**
	 * this function will return all the details of the user corresponding to a particular username and password
	 * @param user	stores the user value object
	 * @return user details of the corresponding user
	 * @throws MetaSqlException
	 */
	public static User authenticate(User user) throws MetaSqlException {
		//create and retrieve the connection from the  ConnectionPoolFactory
		ConnectionPoolFactory cp = ConnectionPoolFactory.getInstance();
		Connection con = cp.checkOut();
		
		String uname = user.getUsername();
		String password = user.getPassword();
		ResultSet rs = null;
		User loggedUser = null;
		
		String query = " SELECT * FROM metacubegranth_user WHERE USER_NAME = ? AND PASSWORD = ? ";
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(query);
			/* set variable in prepared statement */
			ps.setString(1, uname);
			ps.setString(2, password);
			rs = ps.executeQuery();
			loggedUser = processResultSet(rs);
		} catch (SQLException e) {
			//if there is any sql exception  then the exception is thrown
			throw new MetaSqlException(e);
		} finally {
			//close all the open database connection
			Util.closeStatement(ps);
			//checkIn the connection again to the ConnectionPoolFactory
			 cp.checkIn(con);
		}
		return loggedUser;
	}
	
	/**
	 * this function will return the user details on the basis of userId
	 * @param userId of the user
	 * @return	details of the user on the basis of the userId
	 * @throws MetaSqlException
	 */
	public static User getUserById(int userId) throws MetaSqlException {
		//create and retrieve connection from the ConnectionPoolFactory
		ConnectionPoolFactory cp = ConnectionPoolFactory.getInstance();
		Connection con = cp.checkOut();
		
		ResultSet rs = null;
		User user = null;
		
		String query = " SELECT * FROM metacubegranth_user WHERE USER_IDENTIFICATION_NUMBER=?";
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(query);
			/* set variable in prepared statement */
			ps.setInt(1, userId);
			rs = ps.executeQuery();
			user = processResultSet(rs);
		} catch (SQLException e) {
			//if there is any sql exception  then the exception is thrown
			throw new MetaSqlException(e);
		} finally {
			//close all the open database connection
			Util.closeStatement(ps);
			//checkIn the connection again to the ConnectionPoolFactory
			 cp.checkIn(con);
		}
		return user;
	}

	/**
	 * this function is used to update the password and other details of the user
	 * @param user	object of the user whose credentials has to be changed
	 * @return	the status whether the row has been updated or not
	 * @throws MetaSqlException 
	 */
	public static boolean changeCredentials(User user) throws MetaSqlException {
		//create and retrieve the connection from the ConnectionPoolFactory
		ConnectionPoolFactory cp = ConnectionPoolFactory.getInstance();
		Connection con = cp.checkOut();
		
		boolean result = false;
		Statement st = null;
		try {
			String query = " update metacubegranth_user set ";
			if (user.getUsername().isEmpty()) {
				return false;
			}
			if (!user.getPassword().isEmpty()) {
				query = query + " password = '" + user.getPassword()+ "'";
			}

			query = query + " WHERE USER_NAME= '" + user.getUsername()+ "'";
			System.out.println(query);
			st = (Statement) con.createStatement();
			int status = st.executeUpdate(query);
			if (status == 1) {
				result = true;
			} else
				result = false;
		} catch (SQLException e) {
			//if there is any sql exception  then the exception is thrown
			throw new MetaSqlException(e);
		} finally {
			//close all the open database connection
			Util.closeStatement(st);
			//checkIn the connection again to the ConnectionPoolFactory
			 cp.checkIn(con);
		}
		return result;

	}
	
	/**
	 * 
	 * @param rs stores the resultSet after the query has been executed
	 * @return the user value object
	 * @throws MetaSqlException
	 */
	private static User processResultSet(ResultSet rs) throws MetaSqlException {
		User user = null;
		try {
			if (rs.next()) {
				user = new User();
				user.setUid(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setName(rs.getString(4));
				user.setAge(rs.getInt(5));
				user.setLastlogindate(rs.getDate(6));
				user.setType(rs.getInt(7));
			}
		} catch (SQLException e) {
			//if there is any sql exception  then the exception is thrown
			throw new MetaSqlException(e);
		}
		return user;
	}
}
